package com.bpms.common;

import java.io.ByteArrayOutputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.sql.CLOB;

/**
 * JDBC 简单封装类
 * 
 * @author menlin
 * 
 */
public class DBOperation {
	private ResultSet rs;
	private Statement stm;
	private Connection con;
	private String url = "";
	private String classname = "";
	private String username = "";
	private String password = "";

	/*----------------------------------------------------*/
	/**
	 * 构造函数
	 * 
	 * @throws Exception
	 */
	public DBOperation() {
		// 获取数据库配置用户名密码
		PropertyReader reader = PropertyReader
				.getHolder("/dbconfig.properties");
		classname = reader.getString("JDBC.Driver");
		url = reader.getString("JDBC.ConnectionURL");
		username = reader.getString("JDBC.Username");
		password = reader.getString("JDBC.Password");
		try {
			// 加载数据库驱动
			Class.forName(classname);
		} catch (ClassNotFoundException e) {
			CommonUtil.error("" + e);
		}
	}

	/**
	 * 创建数据库连接
	 * 
	 * @throws Exception
	 */
	public Connection getCon() {
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			CommonUtil.error("" + e);
		}
		return con;
	}

	/*----------------------------------------------------*/
	/**
	 * 获取Statement记录
	 * 
	 * @throws Exception
	 */
	public Statement getStm() {
		con = getCon();
		try {
			stm = con.createStatement();
		} catch (Exception e) {
			CommonUtil.error("" + e);
			this.closed();
		}
		return stm;
	}

	/**
	 * 调法上面的方法，查询数据库，返回单个结果 其他类调用过程如下： DB db=new DB(); ResultSet
	 * r=db.getrs(sql); while(r.next()){ String s1 = r.getInt(1); }
	 * 
	 * @throws Exception
	 */
	public ResultSet getrs(String sql) throws Exception {
		if (sql == null)
			sql = "";
		stm = getStm();
		rs = stm.executeQuery(sql);
		return rs;
	}

	/*----------------------------------------------------*/
	/**
	 * 获取Statement记录集
	 * 
	 * @throws Exception
	 */
	public Statement getStmed() throws Exception {
		con = getCon();
		stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		return stm;
	}

	/**
	 * 调法上面的方法，查询数据库，返回一个结果集 其他类调用过程如下：<br/>
	 * DB db=new DB(); <br>
	 * ResultSet rs=db.getRs(sql); <br>
	 * if(rs.next()){ <br>
	 * String s1 = r.getInt(1);<br>
	 * String s2 = r.getInt(2); <br>
	 * String s3 = r.getInt(3); <br>
	 * 
	 * @throws Exception
	 */
	public ResultSet getRs(String sql) throws Exception {
		if (sql == null)
			sql = "";
		stm = getStmed();
		rs = stm.executeQuery(sql);
		return rs;
	}

	/*----------------------------------------------------*/
	/**
	 * 对数据库进行更新操作，适合SQL的insert语句和update语句 返回一个int值，表示更新的记录数 若返回为0,表示更新失败
	 * 其他类调用过程如下： DB db=new DB(); int i=db.update(sql); f(i==0){ return
	 * mapping.findForward("false"); } return mapping.findForward("success");
	 */
	public int update(String sql) {
		int num = 0;
		if (sql == null)
			sql = "";
		try {
			stm = getStmed();
			num = stm.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
			num = 0;
		}
		return num;
	}

	/*----------------------------------------------------*/
	/**
	 * 删除数据库的中数据 其他类调用过程如下： DB db=new DB(); db.delete(sql);
	 * 
	 * @throws Exception
	 */
	public boolean delete(String sql) throws Exception {
		boolean ok;
		if (sql == null)
			sql = "";
		stm = getStmed();
		ok = stm.execute(sql);
		return ok;
	}

	/**
	 * 断开数据库连接 其他类调用过程如下： DB db=new DB(); db.closed();
	 * 
	 * @throws Exception
	 */
	public void closed() {
		if (rs != null) {
			try {
				if (!rs.isClosed())
					rs.close();
			} catch (SQLException e) {
				CommonUtil.error(e.toString());
			}
			rs = null;
		}
		if (stm != null) {
			try {
				if (!stm.isClosed())
					stm.close();
			} catch (SQLException e) {
				CommonUtil.error(e.toString());
			}
			stm = null;
		}
		if (con != null) {
			try {
				if (!con.isClosed())
					con.close();
			} catch (SQLException e) {
				CommonUtil.error(e.toString());
			}
			con = null;
		}
	}
	
	/**
	 * clob to string 大字符串格式转换STRING
	 * 
	 * @param clob
	 * @return 大字符串
	 * 
	 */
	public String Clob2String(CLOB clob) {// Clob转换成String 的方法
		String content = null;
		StringBuffer stringBuf = new StringBuffer();
		try {
			int length = 0;
			Reader inStream = clob.getCharacterStream(); // 取得大字侧段对象数据输出流
			char[] buffer = new char[10];
			while ((length = inStream.read(buffer)) != -1) // 读取数据库 //每10个10个读取
			{
				for (int i = 0; i < length; i++) {
					stringBuf.append(buffer[i]);
				}
			}
			inStream.close();
			content = stringBuf.toString();
		} catch (Exception ex) {
			System.out.println("ClobUtil.Clob2String:" + ex.getMessage());
		}
		return content;
	}
	
	
}
